package com.etone.H2;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import junit.framework.TestCase;

import org.h2.tools.Csv;

public class Export2Csv extends TestCase{

	public static Connection IQConn = null;
	public static Connection H2Conn = null;
	
	public static File TestFile = new File("F:/test2.csv");
	
	public static String[] ColumnNames= {
		"intProtocol",
		"biSessID",
		"intSubSessID",
		"biSessIDEx",
		"dtSTime",
		"dtETime",
		"intYear",
		"intMonth",
		"intDay",
		"intHour",
		"intMinute",
		"vcSIP",
		"vcDIP",
		"intOPC",
		"intDPC",
		"intMscPC",
		"intBscPC",
		"intFirstLAC",
		"vcFirstCGI",
		"intEndLAC",
		"vcEndCGI",
		"intFirstCIC",
		"vcFirstTMSI",
		"vcEndTMSI",
		"vcCallingIMSI",
		"vcCalledIMSI",
		"vcCallingIMEI",
		"vcCalledIMEI",
		"vcCallingNumber",
		"vcCalledNumber",
		"intSessType",
		"intSubSessType",
		"dtAssignTime",
		"dtAlertTime",
		"dtAnsTime",
		"intResult",
		"intTermEvent",
		"intTermProtocol",
		"vcTermDirect",
		"intTermCause",
		"intSccpCause",
		"intSccpCauseType",
		"intBssRanCause",
		"intRrCause",
		"intMmCause",
		"intCcCause",
		"intCpCause",
		"intRpCause",
		"intSsCause",
		"biMsgFlag",
		"biMsgDirectFlag",
		"biMsgRepeatFlag",
		"biKpiFlag",
		"intPagingCount",
		"intDuration",
		"intSSOpCode",
		"intSSErrCode",
		"intSSCode",
		"intSMSLen",
		"vcSMCAddrForwNumber",
		"intOppMscPc",
		"intOppBscPc",
		"intHoRqd",
		"intHoRqdSucc",
		"intHoRqdFail",
		"intHoRqdRej",
		"intHoPerform"
	};
	
	
	public static synchronized Connection getIQConn(){
		if (IQConn == null) {
			try {
				Class.forName("com.sybase.jdbc3.jdbc.SybDriver");
				String url = "jdbc:sybase:Tds:192.168.8.24:2639/asiqssmp";
				String name = "DBA";
				String pwd = "sql";
				IQConn = DriverManager.getConnection(url, name, pwd);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return IQConn;
	}
	
	
	public static synchronized Connection getH2Conn(){
		if (null == H2Conn) {
			String url = "jdbc:h2:mem:rtmm;" + "DB_CLOSE_DELAY=-1;LOG=0;"
					+ "CACHE_SIZE=1536000;" + "LOCK_MODE=0;UNDO_LOG=0";
			String userName = "sa";
			String pwd = "sa";
			try {
				Class.forName("org.h2.Driver");
				H2Conn = DriverManager.getConnection(url, userName, pwd);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		return H2Conn;
	}
	

	
	public static void testResult2Csv() {
		
		String sql = "select top 10000 * from ftbBssRanSession25_2011070921";
		
		try {
			PreparedStatement pstmt = getIQConn().prepareStatement(sql);
			pstmt.execute();
			ResultSet rs = pstmt.getResultSet();
			Csv.getInstance().write("F:/10K.csv", rs, "utf-8");
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public static void testReadAsCvs() {
		

		H2Function.registerQueryFromReader(getH2Conn());
		
		String sql1 = "select * from CSV_Read()";
		String sql2 = "create table test as select * from CSV_Read()";
		String sql3 = "select count(*) from test";
		try {
			Statement stmt = H2Conn.createStatement();
			
			long t1 = System.currentTimeMillis();			
			stmt.execute(sql1);
			
			long t2 = System.currentTimeMillis();
			
			System.out.println("CSV_read:" + (t2-t1) +"ms");
			
//			stmt.execute(sql2);
			
			
			long t3 = System.currentTimeMillis();			
//			stmt.execute(sql3);
			ResultSet rs = stmt.getResultSet();
			
			System.out.println("create as select:" + (t3-t2) +"ms");
			int count = rs.getMetaData().getColumnCount();
			rs.next();
	        while (rs.next()) {
	            for(int i=1;i<=count;i++){
	            	System.out.print(rs.getString(i));
	            }
	            System.out.println();
	        }
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public static void main(String[] args) {
//		testResult2Csv();
		testReadAsCvs();
	}
	
}
